﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Data.SqlClient;
using ReportWizard.Helpers;

namespace ReportWizard.DataAccess
{
    public class ArchiveReport
    {
        public int Id { get; set; }
        public int UserId { get; set; }
        public int TemplateId { get; set; }
        public string Name { get; set; }
        public DateTime DateCreated { get; set; }
        public bool Deleted { get; set; }

        public string Login { get; private set; }
        public string TemplateName { get; private set; }

        public enum SortingFields
        {
            Name,
            Login,
            TemplateName,
            DateCreated
        }

        private ArchiveReport()
        {
        }

        public ArchiveReport(int userId, int templateId, string name)
        {
            UserId = userId;
            TemplateId = templateId;
            Name = name;
        }

        public void Insert()
        {
            try
            {
                using (SqlConnection con = CommonDb.GetOilWizardConection())
                {
                    SqlCommand saveCmd = new SqlCommand("ArchiveReportInsert", con);
                    saveCmd.CommandType = CommandType.StoredProcedure;

                    saveCmd.Parameters.Add("@UserId", SqlDbType.Int).Value = Globals.CurrentUser.Id;
                    saveCmd.Parameters.Add("@TemplateId", SqlDbType.Int).Value = TemplateId;
                    saveCmd.Parameters.Add("@Name", SqlDbType.VarBinary).Value = Crypto.Encrypt(Name);
                    saveCmd.Parameters.Add("@Id", SqlDbType.Int).Direction = ParameterDirection.Output;

                    con.Open();
                    if (saveCmd.ExecuteNonQuery() != 0)
                    {
                        Id = Convert.ToInt32(saveCmd.Parameters["@Id"].Value);
                    }   
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
            }
        }

        public static DateTime SelectDate(int userId, bool isMaxDate)
        {
            try
            {
                using (SqlConnection con = CommonDb.GetOilWizardConection())
                {
                    SqlCommand getCmd = new SqlCommand
                                            {
                                                Connection = con,
                                                CommandText = isMaxDate ? "ArchiveReportSelectMaxDate" : "ArchiveReportSelectMinDate",
                                                CommandType = CommandType.StoredProcedure
                                            };

                    getCmd.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;

                    con.Open();
                    object selectedDate = getCmd.ExecuteScalar();

                    return selectedDate != DBNull.Value ? Convert.ToDateTime(getCmd.ExecuteScalar()) : DateTime.Now;
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return DateTime.Now;
            }
        }

        public static List<ArchiveReport> Select(int userId, DateTime startDate, DateTime endDate)
        {
            try
            {
                using (SqlConnection con = CommonDb.GetOilWizardConection())
                {
                    SqlCommand getCmd = new SqlCommand("ArchiveReportSelectRange", con);
                    getCmd.CommandType = CommandType.StoredProcedure;

                    getCmd.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
                    getCmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = startDate;
                    getCmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = endDate;

                    con.Open();
                    return MapFrom(getCmd.ExecuteReader());
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return null;
            }
        }

        public static ArchiveReport Select(int id)
        {
            try
            {
                using (SqlConnection con = CommonDb.GetOilWizardConection())
                {
                    SqlCommand getCmd = new SqlCommand("ArchiveReportSelect", con);
                    getCmd.CommandType = CommandType.StoredProcedure;

                    getCmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;

                    con.Open();
                    List<ArchiveReport> lArchiveReport = MapFrom(getCmd.ExecuteReader());
                    return lArchiveReport != null ? lArchiveReport.FirstOrDefault() : null;
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return null;
            }
        }

        private static List<ArchiveReport> MapFrom(SqlDataReader dr)
        {
            try
            {
                if (dr != null && dr.HasRows)
                {
                    List<ArchiveReport> acList = new List<ArchiveReport>();
                    while (dr.Read())
                    {
                        ArchiveReport historyRecord = new ArchiveReport
                        {
                            Id = Convert.ToInt32(dr["Id"]),
                            UserId = dr["UserId"] != DBNull.Value ? Convert.ToInt32(dr["UserId"]) : 0,
                            Login = Crypto.Decrypt((byte[])dr["Login"]),
                            TemplateId = Convert.ToInt32(dr["TemplateId"]),
                            TemplateName = Crypto.Decrypt((byte[])dr["FileName"]),
                            Name = Crypto.Decrypt((byte[])dr["Name"]),
                            DateCreated = Convert.ToDateTime(dr["DateCreated"]),
                            Deleted = Convert.ToBoolean(dr["Deleted"])
                        };
                        acList.Add(historyRecord);
                    }
                    return acList;
                }
                return null;
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return null;
            }
        }
    }
}
